import pymysql
import logging
from impala.dbapi import connect
'''从业务库mysql同步数据到ods时，可以参考该脚本'''
# 旧数据库
conn_old = pymysql.connect(host="192.168.1.173",
                           user="rongyi",
                           password="rongyi123$qwer",
                           db="buorg",
                           port=3306,
                           charset="utf8")
# 以hive作为新数据库
conn_new = connect(host='192.168.10.56',
               port=10000,
               user='work',
               password='work',
               database='log',
               auth_mechanism='PLAIN',
               kerberos_service_name='hive')


# 新旧表字段存放在二维列表中
def db_diff(tb_new, tb_old, *tb_field):
    """
    :param tb_new: 新表
    :param tb_old: 旧表
    :param tb_field: [[新表中字段,],[对应旧表中的字段,]]
    :return: 返回新旧表中的数据总量，以及旧表中存在，但是在新表中没有找到的数据
    """
    # 校验数据总量是否一致
    cmp_new_sql = "select count(*) from " + tb_new +" where topicdate='20200929' "
    cmp_old_sql = "select count(*) from " + tb_old + ";"
    print('验证数据条数sql：',cmp_new_sql,'\n',cmp_old_sql)
    logging.debug(cmp_new_sql)
    logging.debug(cmp_old_sql)
    cursor_old = conn_old.cursor()
    cursor_new = conn_new.cursor()
    cursor_new.execute(cmp_new_sql)
    cursor_old.execute(cmp_old_sql)

    new_num = cursor_new.fetchone()
    old_num = cursor_old.fetchone()

    if new_num == old_num:
        print(tb_new + "和" + tb_old + "数据量相同:", new_num[0],old_num[0])
    else:
        print({tb_new + "_new": new_num[0], tb_old: old_num[0]})

    # 校验各字段值是否一致
    field_new = ", ".join(tb_field[0][0])
    field_old = ", ".join(tb_field[0][1])
    cmp_dt_new_sql = "select " + field_new + " from " + tb_new + " where topicdate='20200929' "
    cmp_dt_old_sql = "select " + field_old + " from " + tb_old + ";"
    print('验证数据列sql：',cmp_dt_new_sql,'\n',cmp_dt_old_sql)
    #logging.debug(cmp_dt_new_sql)
    #logging.debug(cmp_dt_old_sql)
    cursor_new.execute(cmp_dt_new_sql)
    cursor_old.execute(cmp_dt_old_sql)
    new_dt = cursor_new.fetchall()
    old_dt = cursor_old.fetchall()
    #print(list(new_dt))
    #print(list(old_dt))

    count = 0
    for item in old_dt:
        if item in new_dt:
            pass
        else:
            print(tb_new + "新表中未找到:" + str(item))
            count += 1

    print("新表中未找到总数: %d" % count)
    '''从mysql同步到ods时，可能会出现特殊字符导致的列错位或者换行，所以需要校验源表中少的数据'''
    count_old = 0
    for item_old in new_dt:
        if item_old in old_dt:
            pass
        else:
            print(tb_old + "旧表中未找到:" + str(item))
            count_old += 1

    print("旧表中未找到总数: %d" % count_old)
    print("\n")

    # 关闭游标
    cursor_old.close()
    cursor_new.close()
    return



# 测试表
test_table_field = [["id,tenant_id"], ["id,tenant_id"]]
db_diff("ods_buorg.org_branch_info", "org_branch_info", test_table_field)

# 关闭数据库连接
conn_old.close()
conn_new.close()